<!DOCTYPE html>
<html lang="en" class="svg no-js">
<head>
    <meta charset="utf-8" />
    <!--[if IE ]>
    <meta http-equiv="X-UA-Compatible" content="IE=Edge,chrome=1" />
    <![endif]-->
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <meta name="Language" content="en">

    <title>MySQL :: MySQL 8.0 Reference Manual :: 9.5 Expressions</title>
    <link rel="stylesheet" media="all" href="css/main-20190125.min.css" />
            <link rel="stylesheet" media="all" href="css/docs-20190125.min.css" />
    
    
    <link rel="stylesheet" media="print" href="css/print-20190125.min.css" />

    
    
    
    
            <link rel="contents" href="index.html" title="MySQL 8.0 Reference Manual" />
<link rel="start" href="index.html" title="MySQL 8.0 Reference Manual" />
<link rel="prev" href="user-variables.html" title="9.4 User-Defined Variables" />
<link rel="next" href="comments.html?ff=nopfpls" title="9.6 Comment Syntax" />
<link rel="up" href="language-structure.html" title="9 Language Structure" />

    
    <link rel="shortcut icon" href="favicon.ico" />
    <script>(function(H){ H.className=H.className.replace(/\bno-js\b/,'js') })(document.documentElement)</script>
    <script src="js/site-20181120.min.js"></script>

    
    
    
    <!--[if lt IE 9]>
        <script src="https://labs.mysql.com/common/js/polyfills/html5shiv-printshiv-3.7.2.min.js"></script>
        <script src="https://labs.mysql.com/common/js/polyfills/respond-1.4.2.min.js"></script>
    <![endif]-->
        <!--[if IE 9]>
        <style>#docs-sidebar-toc { box-sizing: content-box; }</style>
    <![endif]-->
    </head>

<body class="no-sidebar full-page dev">
<div class="page-wrapper">
    <header>

                        <a href="https://dev.mysql.com/" aria-label="Home" title="MySQL" id="l1-home-link"></a>
        
        <div id="l1-nav-container">
            <div id="l1-line1">
                <div id="l1-auth-links">
                    <a href="https://www.mysql.com/about/contact/"><b>Contact MySQL</b></a>
                    <span id="l1-contact-separator">&nbsp;|&nbsp;</span>
                    <span id="l1-contact-separator-br"><br /></span>
                                             <a href="https://dev.mysql.com/auth/login/?dest=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F8.0%2Fen%2Fexpressions.html">Login</a> &nbsp;|&nbsp;
                         <a href="https://dev.mysql.com/auth/register/">Register</a>
                                    </div>
                <div id="l1-lhs">
                    <div id="l1-slogan">
                        The world's most popular open source database
                    </div>
                    <div id="l1-search-box">
                        <form id="l1-search-form" method="get" action="https://www.oracle.com/search/results">
                        <input type="hidden" name="cat" value="mysql" />
                        <input type="hidden" name="Ntk" value="SI-ALL5" />
                        <input id="l1-search-input" type="search" class="icon-search" placeholder="Search" aria-label="Search" name="Ntt" />
                        </form>
                    </div>
                </div>
            </div>
            <div id="l1-line2">
                <div class="social-icons">
                    <a aria-label="Join us on Facebook" title="Join us on Facebook" href="http://www.facebook.com/mysql"><span class="icon-facebook"></span></a>
                    <a aria-label="Follow us on Twitter" title="Follow us on Twitter" href="https://twitter.com/mysql"><span class="icon-twitter"></span></a>
                    <a aria-label="Follow us on LinkedIn" title="Follow us on LinkedIn" href="https://www.linkedin.com/company/mysql"><span class="icon-linkedin"></span></a>
                    <a aria-label="Visit our YouTube channel" title="Visit our YouTube channel" href="http://www.youtube.com/mysqlchannel"><span class="icon-youtube"></span></a>
                </div>
                <ul id="l1-nav">
                    <li>
                        <a href="https://www.mysql.com/"><!-- <span class="icon-sakila"></span>  -->MySQL.com</a>
                    </li><li>
                        <a href="https://www.mysql.com/downloads/"><!-- <span class="icon-download-thin"></span>  -->Downloads</a>
                    </li><li class="active">
                        <a href="/doc/"><!-- <span class="icon-books"></span>  -->Documentation</a>
                    </li><li>
                        <a href="/"><!-- <span class="icon-code"></span>  -->Developer Zone</a>
                    </li>                </ul>
            </div>
        </div>
        <div id="sub-header">
            <div id="l2-nav-container">
                <div id="l2-nav-toggle">
                    <span class="icon-three-bars"></span>
                </div>
                                                                                        <a class="button nav-button-3" href="/"><span class="icon-code"></span> Developer Zone</a>
                                                                                                                                    <a class="button nav-button-2" href="https://www.mysql.com/downloads/"><span class="icon-download-thin"></span> Downloads</a>
                                                                                                <a class="button nav-button-1" href="https://www.mysql.com/"><span class="icon-sakila"></span> MySQL.com</a>
                                                                        <div id="l2-search-toggle">
                    <span class="icon-search"></span>
                </div>
                <div id="l2-site-icon">
                                                                                                                                                                                                    <span class="icon-books"></span>
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        </div>
                <div id="l1-section-title">
                    <span id="l1-section-label">
                        <a href="/doc/">Documentation</a>
                    </span>
                </div>
                <nav>
                    

<ul id="l2-nav">
            	<li class="active"><a class="active " href="/doc/refman/en/">MySQL Server</a>
    			</li>
	        	<li class=""><a  href="/doc/index-enterprise.html">MySQL Enterprise</a>
    			</li>
	        	<li class=""><a  href="/doc/workbench/en/">Workbench</a>
    			</li>
	        	<li class=""><a  href="/doc/en/mysql-innodb-cluster-userguide.html">InnoDB Cluster</a>
    			</li>
	        	<li class=""><a  href="/doc/index-cluster.html">MySQL NDB Cluster</a>
    			</li>
	        	<li class=""><a  href="/doc/index-connectors.html">Connectors</a>
    			</li>
	        	<li class=" last"><a  href="/doc/index-other.html">More</a>
    			</li>
	                        <li class="other-section"><a href="https://www.mysql.com/">MySQL.com</a></li>
                            <li class="other-section"><a href="https://www.mysql.com/downloads/">Downloads</a></li>
                                        <li class="other-section"><a href="/">Developer Zone</a></li>
            </ul>
                </nav>
            </div>
            <div id="l3-search-container"></div>
            <div id="top-orange"><span id="section-nav">Section Menu: &nbsp; </span></div>
        </div>
    </header>
        <div id="page">
                        <div role="main" id="main">

            
                
            
<div>
    <div id="docs-sidebar-toc" class="">
    <div id="docs-toc-inner">
        <div class="docs-sidebar-header" id="docs-nav-header">
            <a class="docs-show-hide-nav" id="docs-hide-nav" href="" title="Hide Sidebar" aria-label="Hide Sidebar"><span class="icon-arrow-small-left"></span></a>
            <div class="docs-nav-links">
                                    
    <a href="user-variables.html"
        aria-label="Previous" title="Previous: User-Defined Variables"><span
        class="icon-chevron-left"></span></a>
<a href="index.html" aria-label="Start" title="Start"><span class="icon-book-open"></span></a>
        <a aria-label="Up" href="language-structure.html" title="Up: Language Structure"><span class="icon-chevron-up"></span></a>
    <a href="comments.html" aria-label="Next"
        title="Next: Comment Syntax"><span
        class="icon-chevron-right"></span></a>
                            </div>
        </div>
                    
<div id="docs-sidebar-search-container">
    <div id="docs-sidebar-search-box">
        <form method="get" action="/mysql-manual/search-page">
            <input type="hidden" name="d" id="d" value="201" />
            <input type="hidden" name="p" id="p" value="1" />
            <input type="text" name="keyword" id="q" title="Search this Manual"
                value=""
                style="color: #bbb;"
                onfocus=""
                onblur="" />

            <button class="docs-sidebar-search-btn" aria-label="Search" title="Search" type="submit">
                <span class="icon-search"></span>
            </button>
        </form>
    </div>
</div>
                <div class="docs-sidebar-nav">
            <a class="docs-icon-home" href="/doc/"><span class="icon-home"></span>Documentation Home</a><hr />
            <div class="docs-sidebar-mtitle">MySQL 8.0 Reference Manual</div>
            <nav class="doctoc" id="doc-201">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="preface.html">Preface and Legal Notices</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="introduction.html">General Information</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="installing.html">Installing and Upgrading MySQL</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="tutorial.html">Tutorial</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="programs.html">MySQL Programs</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="server-administration.html">MySQL Server Administration</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="security.html">Security</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="backup-and-recovery.html">Backup and Recovery</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="optimization.html">Optimization</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-down"></span></a></div><div class="docs-sidebar-nav-link"><a href="language-structure.html">Language Structure</a></div></div>                        <div class="docs-submenu">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="literals.html">Literal Values</a></div></div>                        <div class="docs-submenu hidden">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="string-literals.html">String Literals</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="number-literals.html">Numeric Literals</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="date-and-time-literals.html">Date and Time Literals</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="hexadecimal-literals.html">Hexadecimal Literals</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="bit-value-literals.html">Bit-Value Literals</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="boolean-literals.html">Boolean Literals</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="null-values.html">NULL Values</a></div></div>                    </li>
        </ul>
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable  loaded" aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="identifiers.html">Schema Object Names</a></div></div>                        <div class="docs-submenu hidden">
                                    
    <ul>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="identifier-qualifiers.html">Identifier Qualifiers</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="identifier-case-sensitivity.html">Identifier Case Sensitivity</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="identifier-mapping.html">Mapping of Identifiers to File Names</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="function-resolution.html">Function Name Parsing and Resolution</a></div></div>                    </li>
        </ul>
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="keywords.html">Keywords and Reserved Words</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="user-variables.html">User-Defined Variables</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link current"><a href="expressions.html">Expressions</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="comments.html">Comment Syntax</a></div></div>                    </li>
        </ul>
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="charset.html">Character Sets, Collations, Unicode</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="data-types.html">Data Types</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="functions.html">Functions and Operators</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="sql-syntax.html">SQL Statement Syntax</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="data-dictionary.html">MySQL Data Dictionary</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="innodb-storage-engine.html">The InnoDB Storage Engine</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="storage-engines.html">Alternative Storage Engines</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="replication.html">Replication</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="group-replication.html">Group Replication</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="mysql-shell-userguide.html">MySQL Shell</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="document-store.html">Using MySQL as a Document Store</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="mysql-innodb-cluster-userguide.html">InnoDB Cluster</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="mysql-cluster.html">MySQL NDB Cluster 8.0</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="partitioning.html">Partitioning</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="stored-objects.html">Stored Objects</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="information-schema.html">INFORMATION_SCHEMA Tables</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="performance-schema.html">MySQL Performance Schema</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="sys-schema.html">MySQL sys Schema</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="connectors-apis.html">Connectors and APIs</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="extending-mysql.html">Extending MySQL</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="mysql-enterprise.html">MySQL Enterprise Edition</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="workbench.html">MySQL Workbench</a></div></div>                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="faqs.html">MySQL 8.0 Frequently Asked Questions</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="error-handling.html">Errors, Error Codes, and Common Problems</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="restrictions.html">Restrictions and Limits</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><a class="expandable " aria-label="Subnav" href=""><span class="icon-chevron-right"></span></a></div><div class="docs-sidebar-nav-link"><a href="indexes.html">Indexes</a></div></div>                        <div class="docs-submenu hidden">
                            </div>
                    </li>
            <li>
            <div><div class="docs-sidebar-nav-icon"><span class="icon-dot"></span></div><div class="docs-sidebar-nav-link"><a href="glossary.html">MySQL Glossary</a></div></div>                    </li>
        </ul>
                            </nav>
        </div>

        
<a class="docs-sidebar-section" href=""><span class="icon-related"></span>
    <span class="text">Related Documentation</span></a>
<div class="docs-sidebar-accordian open">
    <div class="text">
        <a href="/doc/relnotes/mysql/8.0/en/">MySQL 8.0 Release Notes</a><br />
        <a href="/doc/dev/mysql-server/latest/">MySQL 8.0 Source Code Documentation</a><br />
        </div>
</div>

    <a class="docs-sidebar-section" href=""><span class="icon-download-thin"></span>
        <span class="text">
            Download
                            this Manual
                    </span>
    </a>
    <div class="docs-sidebar-accordian open">
        <div class="text">
                        <a href="https://downloads.mysql.com/docs/refman-8.0-en.pdf">PDF (US Ltr)</a>
            - 46.1Mb<br />
                        <a href="https://downloads.mysql.com/docs/refman-8.0-en.a4.pdf">PDF (A4)</a>
            - 46.1Mb<br />
                        <a href="https://downloads.mysql.com/docs/mysql-ref-manual-8.0-en-pdf-1-20190611.noarch.rpm">PDF (RPM)</a>
            - 41.5Mb<br />
                        <a href="https://downloads.mysql.com/docs/refman-8.0-en.html-chapter.tar.gz">HTML Download (TGZ)</a>
            - 10.6Mb<br />
                        <a href="https://downloads.mysql.com/docs/refman-8.0-en.html-chapter.zip">HTML Download (Zip)</a>
            - 10.6Mb<br />
                        <a href="https://downloads.mysql.com/docs/mysql-ref-manual-8.0-en-html-chapter-1-20190611.noarch.rpm">HTML Download (RPM)</a>
            - 9.1Mb<br />
                        <a href="https://downloads.mysql.com/docs/refman-8.0-en.man-gpl.tar.gz">Man Pages (TGZ)</a>
            - 220.4Kb<br />
                        <a href="https://downloads.mysql.com/docs/refman-8.0-en.man-gpl.zip">Man Pages (Zip)</a>
            - 325.8Kb<br />
                        <a href="https://downloads.mysql.com/docs/mysql-8.0.info.gz">Info (Gzip)</a>
            - 4.1Mb<br />
                        <a href="https://downloads.mysql.com/docs/mysql-8.0.info.zip">Info (Zip)</a>
            - 4.1Mb<br />
                    </div>
    </div>

<a class="docs-sidebar-section" href=""><span class="icon-book"></span>
    <span class="text">Excerpts from this Manual</span></a>
<div class="docs-sidebar-accordian">
    <div class="text">
                <a href="/doc/mysql-backup-excerpt/8.0/en/">MySQL Backup and Recovery</a><br />
                <a href="/doc/mysql-g11n-excerpt/8.0/en/">MySQL Globalization</a><br />
                <a href="/doc/mysql-infoschema-excerpt/8.0/en/">MySQL Information Schema</a><br />
                <a href="/doc/mysql-installation-excerpt/8.0/en/">MySQL Installation Guide</a><br />
                <a href="/doc/mysql-security-excerpt/8.0/en/">Security in MySQL</a><br />
                <a href="/doc/mysql-startstop-excerpt/8.0/en/">Starting and Stopping MySQL</a><br />
                <a href="/doc/mysql-linuxunix-excerpt/8.0/en/">MySQL and Linux/Unix</a><br />
                <a href="/doc/mysql-windows-excerpt/8.0/en/">MySQL and Windows</a><br />
                <a href="/doc/mysql-osx-excerpt/8.0/en/">MySQL and OS X</a><br />
                <a href="/doc/mysql-solaris-excerpt/8.0/en/">MySQL and Solaris</a><br />
                <a href="/doc/mysql-sourcebuild-excerpt/8.0/en/">Building MySQL from Source</a><br />
                <a href="/doc/mysql-reslimits-excerpt/8.0/en/">MySQL Restrictions and Limitations</a><br />
                <a href="/doc/mysql-partitioning-excerpt/8.0/en/">MySQL Partitioning</a><br />
                <a href="/doc/mysql-secure-deployment-guide/8.0/en/">MySQL Secure Deployment Guide</a><br />
                <a href="/doc/mysql-tutorial-excerpt/8.0/en/">MySQL Tutorial</a><br />
                <a href="/doc/mysql-perfschema-excerpt/8.0/en/">MySQL Performance Schema</a><br />
                <a href="/doc/mysql-replication-excerpt/8.0/en/">MySQL Replication</a><br />
                <a href="/doc/mysql-repo-excerpt/8.0/en/">Using the MySQL Yum Repository</a><br />
            </div>
</div>

        <br /><span id="wkr"><br /></span>
    </div>
    </div>

    <div id="docs-main" class="has-toc">
    <div id="docs-main-inner">

                <div class="right" id="docs-version-nav">
            <a href="" id="docs-version-nav-toggle">version 8.0
            <span class="icon-chevron-down"></span></a>
            <div id="docs-version-list">
                                                                                            <a                         href="/doc/refman/5.7/en/expressions.html">
                        5.7
                                            </a><br />
                                                                            <a                         href="/doc/refman/5.6/en/expressions.html">
                        5.6
                                            </a><br />
                                                                            <a                         href="/doc/refman/5.5/en/expressions.html">
                        5.5
                                            </a><br />
                                                                        <div id="docs-version-nav-lang">
                                                                                                                                                    <a                                     href="/doc/refman/5.6/ja/expressions.html">
                                    5.6&nbsp;
                                                                            Japanese
                                                                    </a><br />
                                                                                            </div>
                            </div>
        </div>
        
        <div id="docs-show-nav" class="left hidden" style="margin-right: 15px;">
            <a class="docs-show-hide-nav" href="" aria-label="Show Sidebar"
                title="Show Sidebar"><span class="icon-arrow-small-right"></span></a>
        </div>

                        <div id="docs-breadcrumbs">
            <a href="/doc/refman/8.0/en/">MySQL 8.0 Reference Manual</a> &nbsp;/&nbsp;
                                                        <a href="language-structure.html">Language Structure</a> &nbsp;/&nbsp;
                                                                                    Expressions
                                    </div>
        
        <div id="docs-body">
        
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title"><a name="expressions"></a>9.5 Expressions</h2>

</div>

</div>

</div>
<a class="indexterm" name="idm139663205212384"></a><p>
      This section lists the grammar rules that expressions must follow
      in MySQL and provides additional information about the types of
      terms that may appear in expressions.
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><a class="xref" href="expressions.html#expression-syntax" title="Expression Syntax">Expression Syntax</a></p></li><li class="listitem"><p><a class="xref" href="expressions.html#expression-term-notes" title="Expression Term Notes">Expression Term Notes</a></p></li><li class="listitem"><p><a class="xref" href="expressions.html#temporal-intervals" title="Temporal Intervals">Temporal Intervals</a></p></li></ul>
</div>

<div class="simplesect">

<div class="titlepage">
<div>

<div class="simple">
<h3 class="title"><a name="expression-syntax"></a>Expression Syntax</h3>

</div>

</div>

</div>
<p>
        The following grammar rules define expression syntax in MySQL.
        The grammar shown here is based on that given in the
        <code class="filename">sql/sql_yacc.yy</code> file of MySQL source
        distributions. For additional information about some of the
        expression terms, see <a class="xref" href="expressions.html#expression-term-notes" title="Expression Term Notes">Expression Term Notes</a>.
      </p><pre class="programlisting copytoclipboard line-numbers language-sql"><code class="language-sql"><em class="replaceable">expr</em>:
    <em class="replaceable">expr</em> OR <em class="replaceable">expr</em>
  | <em class="replaceable">expr</em> || <em class="replaceable">expr</em>
  | <em class="replaceable">expr</em> XOR <em class="replaceable">expr</em>
  | <em class="replaceable">expr</em> AND <em class="replaceable">expr</em>
  | <em class="replaceable">expr</em> &amp;&amp; <em class="replaceable">expr</em>
  | NOT <em class="replaceable">expr</em>
  | ! <em class="replaceable">expr</em>
  | <em class="replaceable">boolean_primary</em> IS [NOT] {TRUE | FALSE | UNKNOWN}
  | <em class="replaceable">boolean_primary</em>

<em class="replaceable">boolean_primary</em>:
    <em class="replaceable">boolean_primary</em> IS [NOT] NULL
  | <em class="replaceable">boolean_primary</em> &lt;=&gt; <em class="replaceable">predicate</em>
  | <em class="replaceable">boolean_primary</em> <em class="replaceable">comparison_operator</em> <em class="replaceable">predicate</em>
  | <em class="replaceable">boolean_primary</em> <em class="replaceable">comparison_operator</em> {ALL | ANY} (<em class="replaceable">subquery</em>)
  | <em class="replaceable">predicate</em>

<em class="replaceable">comparison_operator</em>: = | &gt;= | &gt; | &lt;= | &lt; | &lt;&gt; | !=

<em class="replaceable">predicate</em>:
    <em class="replaceable">bit_expr</em> [NOT] IN (<em class="replaceable">subquery</em>)
  | <em class="replaceable">bit_expr</em> [NOT] IN (<em class="replaceable">expr</em> [, <em class="replaceable">expr</em>] ...)
  | <em class="replaceable">bit_expr</em> [NOT] BETWEEN <em class="replaceable">bit_expr</em> AND <em class="replaceable">predicate</em>
  | <em class="replaceable">bit_expr</em> SOUNDS LIKE <em class="replaceable">bit_expr</em>
  | <em class="replaceable">bit_expr</em> [NOT] LIKE <em class="replaceable">simple_expr</em> [ESCAPE <em class="replaceable">simple_expr</em>]
  | <em class="replaceable">bit_expr</em> [NOT] REGEXP <em class="replaceable">bit_expr</em>
  | <em class="replaceable">bit_expr</em>

<em class="replaceable">bit_expr</em>:
    <em class="replaceable">bit_expr</em> | <em class="replaceable">bit_expr</em>
  | <em class="replaceable">bit_expr</em> &amp; <em class="replaceable">bit_expr</em>
  | <em class="replaceable">bit_expr</em> &lt;&lt; <em class="replaceable">bit_expr</em>
  | <em class="replaceable">bit_expr</em> &gt;&gt; <em class="replaceable">bit_expr</em>
  | <em class="replaceable">bit_expr</em> + <em class="replaceable">bit_expr</em>
  | <em class="replaceable">bit_expr</em> - <em class="replaceable">bit_expr</em>
  | <em class="replaceable">bit_expr</em> * <em class="replaceable">bit_expr</em>
  | <em class="replaceable">bit_expr</em> / <em class="replaceable">bit_expr</em>
  | <em class="replaceable">bit_expr</em> DIV <em class="replaceable">bit_expr</em>
  | <em class="replaceable">bit_expr</em> MOD <em class="replaceable">bit_expr</em>
  | <em class="replaceable">bit_expr</em> % <em class="replaceable">bit_expr</em>
  | <em class="replaceable">bit_expr</em> ^ <em class="replaceable">bit_expr</em>
  | <em class="replaceable">bit_expr</em> + <em class="replaceable">interval_expr</em>
  | <em class="replaceable">bit_expr</em> - <em class="replaceable">interval_expr</em>
  | <em class="replaceable">simple_expr</em>

<em class="replaceable">simple_expr</em>:
    <em class="replaceable">literal</em>
  | <em class="replaceable">identifier</em>
  | <em class="replaceable">function_call</em>
  | <em class="replaceable">simple_expr</em> COLLATE <em class="replaceable">collation_name</em>
  | <em class="replaceable">param_marker</em>
  | <em class="replaceable">variable</em>
  | <em class="replaceable">simple_expr</em> || <em class="replaceable">simple_expr</em>
  | + <em class="replaceable">simple_expr</em>
  | - <em class="replaceable">simple_expr</em>
  | ~ <em class="replaceable">simple_expr</em>
  | ! <em class="replaceable">simple_expr</em>
  | BINARY <em class="replaceable">simple_expr</em>
  | (<em class="replaceable">expr</em> [, <em class="replaceable">expr</em>] ...)
  | ROW (<em class="replaceable">expr</em>, <em class="replaceable">expr</em> [, <em class="replaceable">expr</em>] ...)
  | (<em class="replaceable">subquery</em>)
  | EXISTS (<em class="replaceable">subquery</em>)
  | {<em class="replaceable">identifier</em> <em class="replaceable">expr</em>}
  | <em class="replaceable">match_expr</em>
  | <em class="replaceable">case_expr</em>
  | <em class="replaceable">interval_expr</em></code></pre><p>
        For operator precedence, see
        <a class="xref" href="operator-precedence.html" title="12.3.1 Operator Precedence">Section 12.3.1, “Operator Precedence”</a>. The precedence and
        meaning of some operators depends on the SQL mode:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            By default, <a class="link" href="logical-operators.html#operator_or"><code class="literal">||</code></a>
            is a logical <a class="link" href="logical-operators.html#operator_or"><code class="literal">OR</code></a> operator. With
            <a class="link" href="sql-mode.html#sqlmode_pipes_as_concat"><code class="literal">PIPES_AS_CONCAT</code></a> enabled,
            <a class="link" href="logical-operators.html#operator_or"><code class="literal">||</code></a> is string
            concatenation, with a precedence between
            <a class="link" href="bit-functions.html#operator_bitwise-xor"><code class="literal">^</code></a> and
            the unary operators.
          </p></li><li class="listitem"><p>
            By default, <a class="link" href="logical-operators.html#operator_not"><code class="literal">!</code></a>
            has a higher precedence than <code class="literal">NOT</code>. With
            <a class="link" href="sql-mode.html#sqlmode_high_not_precedence"><code class="literal">HIGH_NOT_PRECEDENCE</code></a>
            enabled, <a class="link" href="logical-operators.html#operator_not"><code class="literal">!</code></a> and
            <code class="literal">NOT</code> have the same precedence.
</p></li></ul>
</div>
<p>
        See <a class="xref" href="sql-mode.html" title="5.1.11 Server SQL Modes">Section 5.1.11, “Server SQL Modes”</a>.
</p>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h3 class="title"><a name="expression-term-notes"></a>Expression Term Notes</h3>

</div>

</div>

</div>
<p>
        For literal value syntax, see <a class="xref" href="literals.html" title="9.1 Literal Values">Section 9.1, “Literal Values”</a>.
      </p><p>
        For identifier syntax, see <a class="xref" href="identifiers.html" title="9.2 Schema Object Names">Section 9.2, “Schema Object Names”</a>.
      </p><p>
        Variables can be user variables, system variables, or stored
        program local variables or parameters:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
            User variables: <a class="xref" href="user-variables.html" title="9.4 User-Defined Variables">Section 9.4, “User-Defined Variables”</a>
          </p></li><li class="listitem"><p>
            System variables: <a class="xref" href="using-system-variables.html" title="5.1.9 Using System Variables">Section 5.1.9, “Using System Variables”</a>
          </p></li><li class="listitem"><p>
            Stored program local variables:
            <a class="xref" href="declare-local-variable.html" title="13.6.4.1 Local Variable DECLARE Syntax">Section 13.6.4.1, “Local Variable DECLARE Syntax”</a>
          </p></li><li class="listitem"><p>
            Stored program parameters:
            <a class="xref" href="create-procedure.html" title="13.1.17 CREATE PROCEDURE and CREATE FUNCTION Syntax">Section 13.1.17, “CREATE PROCEDURE and CREATE FUNCTION Syntax”</a>
</p></li></ul>
</div>
<p>
        <em class="replaceable"><code>param_marker</code></em> is <code class="literal">?</code>
        as used in prepared statements for placeholders. See
        <a class="xref" href="prepare.html" title="13.5.1 PREPARE Syntax">Section 13.5.1, “PREPARE Syntax”</a>.
      </p><p>
        <code class="literal">(<em class="replaceable"><code>subquery</code></em>)</code>
        indicates a subquery that returns a single value; that is, a
        scalar subquery. See <a class="xref" href="scalar-subqueries.html" title="13.2.11.1 The Subquery as Scalar Operand">Section 13.2.11.1, “The Subquery as Scalar Operand”</a>.
      </p><p>
        <code class="literal">{<em class="replaceable"><code>identifier</code></em>
        <em class="replaceable"><code>expr</code></em>}</code> is ODBC escape syntax
        and is accepted for ODBC compatibility. The value is
        <em class="replaceable"><code>expr</code></em>. The <code class="literal">{</code> and
        <code class="literal">}</code> curly braces in the syntax should be
        written literally; they are not metasyntax as used elsewhere in
        syntax descriptions.
      </p><p>
        <em class="replaceable"><code>match_expr</code></em> indicates a
        <a class="link" href="fulltext-search.html#function_match"><code class="literal">MATCH</code></a> expression. See
        <a class="xref" href="fulltext-search.html" title="12.9 Full-Text Search Functions">Section 12.9, “Full-Text Search Functions”</a>.
      </p><p>
        <em class="replaceable"><code>case_expr</code></em> indicates a
        <a class="link" href="control-flow-functions.html#operator_case"><code class="literal">CASE</code></a> expression. See
        <a class="xref" href="control-flow-functions.html" title="12.4 Control Flow Functions">Section 12.4, “Control Flow Functions”</a>.
      </p><p>
        <em class="replaceable"><code>interval_expr</code></em> represents a temporal
        interval. See <a class="xref" href="expressions.html#temporal-intervals" title="Temporal Intervals">Temporal Intervals</a>.
</p>
</div>
<div class="simplesect">
<div class="titlepage">
<div>
<div class="simple">
<h3 class="title"><a name="temporal-intervals"></a>Temporal Intervals</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm139663205114144"></a><a class="indexterm" name="idm139663205112640"></a><a class="indexterm" name="idm139663205111552"></a><p>
        <em class="replaceable"><code>interval_expr</code></em> in expressions
        represents a temporal interval. Intervals have this syntax:
      </p><pre class="programlisting copytoclipboard line-numbers language-sql one-line"><code class="language-sql">INTERVAL <em class="replaceable">expr</em> <em class="replaceable">unit</em></code></pre><p>
        <em class="replaceable"><code>expr</code></em> represents a quantity.
        <em class="replaceable"><code>unit</code></em> represents the unit for
        interpreting the quantity; it is a specifier such as
        <code class="literal">HOUR</code>, <code class="literal">DAY</code>, or
        <code class="literal">WEEK</code>. The <code class="literal">INTERVAL</code> keyword
        and the <em class="replaceable"><code>unit</code></em> specifier are not case
        sensitive.
      </p><p>
        The following table shows the expected form of the
        <em class="replaceable"><code>expr</code></em> argument for each
        <em class="replaceable"><code>unit</code></em> value.
</p>
<div class="table">
<a name="temporal-interval-arguments"></a><p class="title"><b>Table 9.2 Temporal Interval Expression and Unit Arguments</b></p>
<div class="table-contents">
<table summary="unit values and the expected expr argument for each unit value."><col width="50%"><col width="50%"><thead><tr>
            <th scope="col"><em class="replaceable"><code>unit</code></em> Value</th>
            <th scope="col">Expected <em class="replaceable"><code>expr</code></em> Format</th>
          </tr></thead><tbody><tr>
            <td scope="row"><code class="literal">MICROSECOND</code></td>
            <td><code class="literal">MICROSECONDS</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">SECOND</code></td>
            <td><code class="literal">SECONDS</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">MINUTE</code></td>
            <td><code class="literal">MINUTES</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">HOUR</code></td>
            <td><code class="literal">HOURS</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">DAY</code></td>
            <td><code class="literal">DAYS</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">WEEK</code></td>
            <td><code class="literal">WEEKS</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">MONTH</code></td>
            <td><code class="literal">MONTHS</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">QUARTER</code></td>
            <td><code class="literal">QUARTERS</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">YEAR</code></td>
            <td><code class="literal">YEARS</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">SECOND_MICROSECOND</code></td>
            <td><code class="literal">'SECONDS.MICROSECONDS'</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">MINUTE_MICROSECOND</code></td>
            <td><code class="literal">'MINUTES:SECONDS.MICROSECONDS'</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">MINUTE_SECOND</code></td>
            <td><code class="literal">'MINUTES:SECONDS'</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">HOUR_MICROSECOND</code></td>
            <td><code class="literal">'HOURS:MINUTES:SECONDS.MICROSECONDS'</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">HOUR_SECOND</code></td>
            <td><code class="literal">'HOURS:MINUTES:SECONDS'</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">HOUR_MINUTE</code></td>
            <td><code class="literal">'HOURS:MINUTES'</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">DAY_MICROSECOND</code></td>
            <td><code class="literal">'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">DAY_SECOND</code></td>
            <td><code class="literal">'DAYS HOURS:MINUTES:SECONDS'</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">DAY_MINUTE</code></td>
            <td><code class="literal">'DAYS HOURS:MINUTES'</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">DAY_HOUR</code></td>
            <td><code class="literal">'DAYS HOURS'</code></td>
          </tr><tr>
            <td scope="row"><code class="literal">YEAR_MONTH</code></td>
            <td><code class="literal">'YEARS-MONTHS'</code></td>
</tr></tbody></table>
</div>

</div>
<br class="table-break"><p>
        MySQL permits any punctuation delimiter in the
        <em class="replaceable"><code>expr</code></em> format. Those shown in the table
        are the suggested delimiters.
      </p><p>
        Temporal intervals are used for certain functions, such as
        <a class="link" href="date-and-time-functions.html#function_date-add"><code class="literal">DATE_ADD()</code></a> and
        <a class="link" href="date-and-time-functions.html#function_date-sub"><code class="literal">DATE_SUB()</code></a>:
      </p><pre class="programlisting copytoclipboard line-numbers language-sql"><code class="language-sql">mysql&gt; SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY);
        -&gt; '2018-05-02'
mysql&gt; SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR);
        -&gt; '2017-05-01'
mysql&gt; SELECT DATE_ADD('2020-12-31 23:59:59',
    -&gt;                 INTERVAL 1 SECOND);
        -&gt; '2021-01-01 00:00:00'
mysql&gt; SELECT DATE_ADD('2018-12-31 23:59:59',
    -&gt;                 INTERVAL 1 DAY);
        -&gt; '2019-01-01 23:59:59'
mysql&gt; SELECT DATE_ADD('2100-12-31 23:59:59',
    -&gt;                 INTERVAL '1:1' MINUTE_SECOND);
        -&gt; '2101-01-01 00:01:00'
mysql&gt; SELECT DATE_SUB('2025-01-01 00:00:00',
    -&gt;                 INTERVAL '1 1:1:1' DAY_SECOND);
        -&gt; '2024-12-30 22:58:59'
mysql&gt; SELECT DATE_ADD('1900-01-01 00:00:00',
    -&gt;                 INTERVAL '-1 10' DAY_HOUR);
        -&gt; '1899-12-30 14:00:00'
mysql&gt; SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
        -&gt; '1997-12-02'
mysql&gt; SELECT DATE_ADD('1992-12-31 23:59:59.000002',
    -&gt;            INTERVAL '1.999999' SECOND_MICROSECOND);
        -&gt; '1993-01-01 00:00:01.000001'</code></pre><p>
        Temporal arithmetic also can be performed in expressions using
        <code class="literal">INTERVAL</code> together with the
        <a class="link" href="arithmetic-functions.html#operator_plus"><code class="literal">+</code></a> or
        <a class="link" href="arithmetic-functions.html#operator_minus"><code class="literal">-</code></a> operator:
      </p><pre class="programlisting copytoclipboard line-numbers language-sql"><code class="language-sql">date + INTERVAL <em class="replaceable">expr</em> <em class="replaceable">unit</em>
date - INTERVAL <em class="replaceable">expr</em> <em class="replaceable">unit</em></code></pre><p>
        <code class="literal">INTERVAL <em class="replaceable"><code>expr</code></em>
        <em class="replaceable"><code>unit</code></em></code> is permitted on either
        side of the <a class="link" href="arithmetic-functions.html#operator_plus"><code class="literal">+</code></a>
        operator if the expression on the other side is a date or
        datetime value. For the
        <a class="link" href="arithmetic-functions.html#operator_minus"><code class="literal">-</code></a> operator,
        <code class="literal">INTERVAL <em class="replaceable"><code>expr</code></em>
        <em class="replaceable"><code>unit</code></em></code> is permitted only on
        the right side, because it makes no sense to subtract a date or
        datetime value from an interval.
      </p><pre class="programlisting copytoclipboard line-numbers language-sql"><code class="language-sql">mysql&gt; SELECT '2018-12-31 23:59:59' + INTERVAL 1 SECOND;
        -&gt; '2019-01-01 00:00:00'
mysql&gt; SELECT INTERVAL 1 DAY + '2018-12-31';
        -&gt; '2019-01-01'
mysql&gt; SELECT '2025-01-01' - INTERVAL 1 SECOND;
        -&gt; '2024-12-31 23:59:59'</code></pre><p>
        The <a class="link" href="date-and-time-functions.html#function_extract"><code class="literal">EXTRACT()</code></a> function uses the
        same kinds of <em class="replaceable"><code>unit</code></em> specifiers as
        <a class="link" href="date-and-time-functions.html#function_date-add"><code class="literal">DATE_ADD()</code></a> or
        <a class="link" href="date-and-time-functions.html#function_date-sub"><code class="literal">DATE_SUB()</code></a>, but extracts parts
        from the date rather than performing date arithmetic:
      </p><pre class="programlisting copytoclipboard line-numbers language-sql"><code class="language-sql">mysql&gt; SELECT EXTRACT(YEAR FROM '2019-07-02');
        -&gt; 2019
mysql&gt; SELECT EXTRACT(YEAR_MONTH FROM '2019-07-02 01:02:03');
        -&gt; 201907</code></pre><p>
        Temporal intervals can be used in <a class="link" href="create-event.html" title="13.1.13 CREATE EVENT Syntax"><code class="literal">CREATE
        EVENT</code></a> statements:
      </p><pre class="programlisting copytoclipboard line-numbers language-sql"><code class="language-sql">CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    DO
      UPDATE myschema.mytable SET mycol = mycol + 1;</code></pre><p>
        If you specify an interval value that is too short (does not
        include all the interval parts that would be expected from the
        <em class="replaceable"><code>unit</code></em> keyword), MySQL assumes that you
        have left out the leftmost parts of the interval value. For
        example, if you specify a <em class="replaceable"><code>unit</code></em> of
        <code class="literal">DAY_SECOND</code>, the value of
        <em class="replaceable"><code>expr</code></em> is expected to have days, hours,
        minutes, and seconds parts. If you specify a value like
        <code class="literal">'1:10'</code>, MySQL assumes that the days and hours
        parts are missing and the value represents minutes and seconds.
        In other words, <code class="literal">'1:10' DAY_SECOND</code> is
        interpreted in such a way that it is equivalent to
        <code class="literal">'1:10' MINUTE_SECOND</code>. This is analogous to
        the way that MySQL interprets
        <a class="link" href="time.html" title="11.3.2 The TIME Type"><code class="literal">TIME</code></a> values as representing
        elapsed time rather than as a time of day.
      </p><p>
        <em class="replaceable"><code>expr</code></em> is treated as a string, so be
        careful if you specify a nonstring value with
        <code class="literal">INTERVAL</code>. For example, with an interval
        specifier of <code class="literal">HOUR_MINUTE</code>, '6/4' is treated as
        6 hours, four minutes, whereas <code class="literal">6/4</code> evaluates
        to <code class="literal">1.5000</code> and is treated as 1 hour, 5000
        minutes:
      </p><pre class="programlisting copytoclipboard line-numbers language-sql"><code class="language-sql">mysql&gt; SELECT '6/4', 6/4;
        -&gt; 1.5000
mysql&gt; SELECT DATE_ADD('2019-01-01', INTERVAL '6/4' HOUR_MINUTE);
        -&gt; '2019-01-01 06:04:00'
mysql&gt; SELECT DATE_ADD('2019-01-01', INTERVAL 6/4 HOUR_MINUTE);
        -&gt; '2019-01-04 12:20:00'</code></pre><p>
        To ensure interpretation of the interval value as you expect, a
        <a class="link" href="cast-functions.html#function_cast"><code class="literal">CAST()</code></a> operation may be used. To
        treat <code class="literal">6/4</code> as 1 hour, 5 minutes, cast it to a
        <a class="link" href="fixed-point-types.html" title="11.2.2 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC"><code class="literal">DECIMAL</code></a> value with a single
        fractional digit:
      </p><pre class="programlisting copytoclipboard line-numbers language-sql"><code class="language-sql">mysql&gt; SELECT CAST(6/4 AS DECIMAL(3,1));
        -&gt; 1.5
mysql&gt; SELECT DATE_ADD('1970-01-01 12:00:00',
    -&gt;                 INTERVAL CAST(6/4 AS DECIMAL(3,1)) HOUR_MINUTE);
        -&gt; '1970-01-01 13:05:00'</code></pre><p>
        If you add to or subtract from a date value something that
        contains a time part, the result is automatically converted to a
        datetime value:
      </p><pre class="programlisting copytoclipboard line-numbers language-sql"><code class="language-sql">mysql&gt; SELECT DATE_ADD('2023-01-01', INTERVAL 1 DAY);
        -&gt; '2023-01-02'
mysql&gt; SELECT DATE_ADD('2023-01-01', INTERVAL 1 HOUR);
        -&gt; '2023-01-01 01:00:00'</code></pre><p>
        If you add <code class="literal">MONTH</code>,
        <code class="literal">YEAR_MONTH</code>, or <code class="literal">YEAR</code> and
        the resulting date has a day that is larger than the maximum day
        for the new month, the day is adjusted to the maximum days in
        the new month:
      </p><pre class="programlisting copytoclipboard line-numbers language-sql"><code class="language-sql">mysql&gt; SELECT DATE_ADD('2019-01-30', INTERVAL 1 MONTH);
        -&gt; '2019-02-28'</code></pre><p>
        Date arithmetic operations require complete dates and do not
        work with incomplete dates such as
        <code class="literal">'2016-07-00'</code> or badly malformed dates:
      </p><pre class="programlisting copytoclipboard line-numbers language-sql"><code class="language-sql">mysql&gt; SELECT DATE_ADD('2016-07-00', INTERVAL 1 DAY);
        -&gt; NULL
mysql&gt; SELECT '2005-03-32' + INTERVAL 1 MONTH;
        -&gt; NULL</code></pre>
</div>

</div>
<br />
        </div>

                <div id="docs-in-page-nav-container">
            <div id="docs-in-page-nav">
                
    <a href="user-variables.html"
        aria-label="Previous" title="Previous: User-Defined Variables"><span
        class="icon-chevron-left"></span> PREV</a> &nbsp;
<a href="index.html" aria-label="Start" title="Start"> HOME</a> &nbsp;
        <a aria-label="Up" href="language-structure.html" title="Up: Language Structure"> UP</a> &nbsp;
    <a href="comments.html" aria-label="Next"
        title="Next: Comment Syntax">NEXT <span
        class="icon-chevron-right"></span></a>
            </div>
        </div>
        
         <div id="docs-body-extra">
             
<a class="docs-sidebar-section" href=""><span class="icon-related"></span>
    <span class="text">Related Documentation</span></a>
<div class="docs-sidebar-accordian">
    <div class="text">
        <a href="/doc/relnotes/mysql/8.0/en/">MySQL 8.0 Release Notes</a><br />
        <a href="/doc/dev/mysql-server/latest/">MySQL 8.0 Source Code Documentation</a><br />
        </div>
</div>

    <a class="docs-sidebar-section" href=""><span class="icon-download-thin"></span>
        <span class="text">
            Download
                            this Manual
                    </span>
    </a>
    <div class="docs-sidebar-accordian">
        <div class="text">
                        <a href="https://downloads.mysql.com/docs/refman-8.0-en.pdf">PDF (US Ltr)</a>
            - 46.1Mb<br />
                        <a href="https://downloads.mysql.com/docs/refman-8.0-en.a4.pdf">PDF (A4)</a>
            - 46.1Mb<br />
                        <a href="https://downloads.mysql.com/docs/mysql-ref-manual-8.0-en-pdf-1-20190611.noarch.rpm">PDF (RPM)</a>
            - 41.5Mb<br />
                        <a href="https://downloads.mysql.com/docs/refman-8.0-en.html-chapter.tar.gz">HTML Download (TGZ)</a>
            - 10.6Mb<br />
                        <a href="https://downloads.mysql.com/docs/refman-8.0-en.html-chapter.zip">HTML Download (Zip)</a>
            - 10.6Mb<br />
                        <a href="https://downloads.mysql.com/docs/mysql-ref-manual-8.0-en-html-chapter-1-20190611.noarch.rpm">HTML Download (RPM)</a>
            - 9.1Mb<br />
                        <a href="https://downloads.mysql.com/docs/refman-8.0-en.man-gpl.tar.gz">Man Pages (TGZ)</a>
            - 220.4Kb<br />
                        <a href="https://downloads.mysql.com/docs/refman-8.0-en.man-gpl.zip">Man Pages (Zip)</a>
            - 325.8Kb<br />
                        <a href="https://downloads.mysql.com/docs/mysql-8.0.info.gz">Info (Gzip)</a>
            - 4.1Mb<br />
                        <a href="https://downloads.mysql.com/docs/mysql-8.0.info.zip">Info (Zip)</a>
            - 4.1Mb<br />
                    </div>
    </div>

<a class="docs-sidebar-section" href=""><span class="icon-book"></span>
    <span class="text">Excerpts from this Manual</span></a>
<div class="docs-sidebar-accordian">
    <div class="text">
                <a href="/doc/mysql-backup-excerpt/8.0/en/">MySQL Backup and Recovery</a><br />
                <a href="/doc/mysql-g11n-excerpt/8.0/en/">MySQL Globalization</a><br />
                <a href="/doc/mysql-infoschema-excerpt/8.0/en/">MySQL Information Schema</a><br />
                <a href="/doc/mysql-installation-excerpt/8.0/en/">MySQL Installation Guide</a><br />
                <a href="/doc/mysql-security-excerpt/8.0/en/">Security in MySQL</a><br />
                <a href="/doc/mysql-startstop-excerpt/8.0/en/">Starting and Stopping MySQL</a><br />
                <a href="/doc/mysql-linuxunix-excerpt/8.0/en/">MySQL and Linux/Unix</a><br />
                <a href="/doc/mysql-windows-excerpt/8.0/en/">MySQL and Windows</a><br />
                <a href="/doc/mysql-osx-excerpt/8.0/en/">MySQL and OS X</a><br />
                <a href="/doc/mysql-solaris-excerpt/8.0/en/">MySQL and Solaris</a><br />
                <a href="/doc/mysql-sourcebuild-excerpt/8.0/en/">Building MySQL from Source</a><br />
                <a href="/doc/mysql-reslimits-excerpt/8.0/en/">MySQL Restrictions and Limitations</a><br />
                <a href="/doc/mysql-partitioning-excerpt/8.0/en/">MySQL Partitioning</a><br />
                <a href="/doc/mysql-secure-deployment-guide/8.0/en/">MySQL Secure Deployment Guide</a><br />
                <a href="/doc/mysql-tutorial-excerpt/8.0/en/">MySQL Tutorial</a><br />
                <a href="/doc/mysql-perfschema-excerpt/8.0/en/">MySQL Performance Schema</a><br />
                <a href="/doc/mysql-replication-excerpt/8.0/en/">MySQL Replication</a><br />
                <a href="/doc/mysql-repo-excerpt/8.0/en/">Using the MySQL Yum Repository</a><br />
            </div>
</div>
         </div>

              </div>
     </div>

</div>

<script>
$(function() {
    var doc = new $.doc({ 'mobile': $.browser.mobile, 'docId': 1, 'highlight': true });
});
</script>
    

            
        </div>
    </div>

    <footer class="collapsed">
        <div id="footer-bottom">
                                    <div id="footer-collapse">
                <a href="" id="expand-footer"
                    aria-label="Expand Footer"
                    title="Expand Footer"><span
                    class="icon-plus-square"></span></a>
            </div>
                        <div class="footer-contact">
                <div id="footer-contact-icon" style="display: none;">
                    <span class="icon-call-phone"></span>
                </div>
                <div id="footer-contact-numbers" style="display: none;">
                    <strong>Contact MySQL Sales</strong><br/>
                    USA/Canada: +1-866-221-0634 &nbsp;
                    (<a href="https://www.mysql.com/about/contact/phone/">More Countries &raquo;</a>)
                </div>
                <div id="footer-contact-copyright" style="display: inline-block;">
                    <a href="http://www.oracle.com/" aria-label="Oracle" title="Oracle"><span class="oracle-logo"></span></a>
                    &nbsp; &copy; 2019, Oracle Corporation and/or its affiliates
                </div>
            </div>
            <div class="social-icons">
                <a aria-label="Join us on Facebook" title="Join us on Facebook" href="http://www.facebook.com/mysql"><span class="icon-facebook-square"></span></a>
                <a aria-label="Follow us on Twitter" title="Follow us on Twitter" href="https://twitter.com/mysql"><span class="icon-twitter-square"></span></a>
                <a aria-label="Follow us on LinkedIn" title="Follow us on LinkedIn" href="https://www.linkedin.com/company/mysql"><span class="icon-linkedin-square"></span></a>
                <a aria-label="Visit our YouTube channel" title="Visit our YouTube channel" href="http://www.youtube.com/mysqlchannel"><span class="icon-youtube-square"></span></a>
            </div>
            <br class="clear" />
        </div>
        <div id="footer-links">

            <div id="footer-nav"></div>

            <div class="links">
                <ul>
                    <li class="top"><a href="https://www.mysql.com/products/">Products</a></li>
                    <li><a href="https://www.mysql.com/cloud/">Oracle MySQL Cloud Service</a></li>
                    <li><a href="https://www.mysql.com/products/enterprise/">MySQL Enterprise Edition</a></li>
                    <li><a href="https://www.mysql.com/products/standard/">MySQL Standard Edition</a></li>
                    <li><a href="https://www.mysql.com/products/classic/">MySQL Classic Edition</a></li>
                    <li><a href="https://www.mysql.com/products/cluster/">MySQL Cluster CGE</a></li>
                    <li><a href="https://www.mysql.com/oem/">MySQL Embedded (OEM/ISV)</a></li>
                </ul>
            </div>

            <div class="links">
                <ul>
                    <li class="top"><a href="https://www.mysql.com/services/">Services</a></li>
                    <li><a href="https://www.mysql.com/training/">Training</a></li>
                    <li><a href="https://www.mysql.com/certification/">Certification</a></li>
                    <li><a href="https://www.mysql.com/consulting/">Consulting</a></li>
                    <li><a href="https://www.mysql.com/support/">Support</a></li>
                </ul>
            </div>

            <div class="links">
                <ul>
                    <li class="top"><a href="https://dev.mysql.com/downloads/">Downloads</a></li>
                    <li><a href="https://dev.mysql.com/downloads/mysql/">MySQL Community Server</a></li>
                    <li><a href="https://dev.mysql.com/downloads/cluster/">MySQL NDB Cluster</a></li>
                    <li><a href="https://dev.mysql.com/downloads/shell/">MySQL Shell</a></li>
                    <li><a href="https://dev.mysql.com/downloads/router/">MySQL Router</a></li>
                    <li><a href="https://dev.mysql.com/downloads/workbench/">MySQL Workbench</a></li>
                </ul>
            </div>

            <div class="links">
                <ul>
                    <li class="top"><a href="https://dev.mysql.com/doc/">Documentation</a></li>
                    <li><a href="https://dev.mysql.com/doc/refman/en/">MySQL Reference Manual</a></li>
                    <li><a href="https://dev.mysql.com/doc/workbench/en/">MySQL Workbench</a></li>
                    <li><a href="https://dev.mysql.com/doc/index-cluster.html">MySQL NDB Cluster</a></li>
                    <li><a href="https://dev.mysql.com/doc/index-connectors.html">MySQL Connectors</a></li>
                    <li><a href="https://dev.mysql.com/doc/#topic">Topic Guides</a></li>
                </ul>
            </div>

            <div class="links">
                <ul>
                    <li class="top"><a href="https://www.mysql.com/about/">About MySQL</a></li>
                    <li><a href="https://www.mysql.com/about/contact/">Contact Us</a></li>
                                                            <li><a href="https://www.mysql.com/buy-mysql/">How to Buy</a></li>
                    <li><a href="https://www.mysql.com/partners/">Partners</a></li>
                    <li><a href="https://www.mysql.com/about/jobs/">Job Opportunities</a></li>
                    <li><a href="https://www.mysql.com/sitemap.html">Site Map</a></li>
                </ul>
            </div>

            
            <div id="footer-logo">
                <a href="http://www.oracle.com/" aria-label="Oracle" title="Oracle"><span class="oracle-logo"></span></a>
                &nbsp; &copy; 2019, Oracle Corporation and/or its affiliates
                <div class="footer-legal-links">
                    <a href="https://www.mysql.com/about/legal/">Legal Policies</a> |
                    <a href="http://www.oracle.com/us/legal/privacy/index.htm">Your Privacy Rights</a> |
                    <a href="http://www.oracle.com/us/legal/terms/index.html">Terms of Use</a> |
                    <a href="http://www.oracle.com/us/legal/third-party-trademarks/index.html">Trademark Policy</a> |
                    <a href="http://www.oracle.com/technetwork/community/oca-486395.html">Contributor Agreement</a> |
                    <div id="teconsent" style="display: inline-block"><script async="async" type="text/javascript" src="js/notice.js" crossorigin=""></script></div>
                </div>
            </div>

        </div>
    </footer>
</div>

        <script src="js/s_code_remote.js"></script>

        
    
</body>
</html>
